package com.netease.mapper;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.netease.RawApiCall;
import com.netease.bo.ModelTableBO;
import com.netease.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * @projectName: modeling-dev
 * @package: com.netease.mapper
 * @className: ModelTableMapper
 * @author: aochong
 * @description: 模型表Mapper(Mapper层也称为Dao层)
 * @date: 2023/4/24 16:32
 * @version: 1.0
 */
@Repository
public class ModelTableMapper {

    private static Logger logger = LoggerFactory.getLogger(ModelTableMapper.class);

    public String builderTableDdl(ModelTableBO modelTableBO){

        if (modelTableBO.getEngine() == null || "".equals(modelTableBO.getEngine())){
            return budilerGpTableDdl(modelTableBO);
        }

        return budilerHiveTableDdl(modelTableBO);
    }

    public String builderTableDml(ModelTableBO modelTableBO){
        return builderGpTableDml(modelTableBO);
    }

    /**
     * @param modelTableBO:
     * @return String
     * @author aochong
     * @description 构建GP引擎表DDL语句
     * @date 2023/6/26 17:09
     */
    public String budilerGpTableDdl(ModelTableBO modelTableBO){
        StringBuilder sb = new StringBuilder("drop table if exists ");
        StringBuilder schema = new StringBuilder();

        //todo 构建表DDL（需要利用设计模式）
        try {
            //获取飞书token
            String tenantToken = RawApiCall.getTenantToken();

            String spreadsheets = modelTableBO.getUrl().split("/")[4].split("\\?")[0];

            //获取工作表
            JSONObject workSheet = RawApiCall.getWorksheet(spreadsheets);

            JSONArray sheets = workSheet.getJSONArray("sheets");
            HashMap<String, JSONObject> sheetMap = new HashMap<>();
            for (int i = 0; i < sheets.size(); i++) {
                JSONObject sheet = sheets.getJSONObject(i);
                sheetMap.put(sheet.getString("title"),sheet);
            }

            String docTitle = RawApiCall.getDocumentInfo(spreadsheets).getJSONObject("data").getJSONObject("spreadsheet").getString("title");

            if (docTitle.contains("EDW")){
                schema.append("dw.");
            }else if(docTitle.contains("ODS")){
                schema.append("ods.");
            }else if(docTitle.contains("DWS")){
                schema.append("dws.");
            }else if(docTitle.contains("ADS")){
                schema.append("ads.");
            }

            //追加schema
            sb.append(schema);

            //获取表名，表注释信息
            JSONObject tblHeaderJson = RawApiCall.getCellValue(spreadsheets, RawApiCall.getSheetId(modelTableBO.getMdlTblChnsName(),sheetMap) + "!B3:C4");
            System.out.println(tblHeaderJson);
            JSONArray headers = tblHeaderJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");

            String tblEngName = "";
            String tblComment = "";

            //
            for (int i = 0; i < headers.size(); i++) {
                if (i == 0) {
                    tblEngName = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
                    sb.append(tblEngName).append(";");
                    sb.append("<BR />").append("create table if not exists ").append(schema).append(tblEngName).append("(");
                }else if(i == headers.size()-1){
                    tblComment = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
                }
            }

            //获取字段信息
            JSONObject tblBodyJson = RawApiCall.getCellValue(spreadsheets, RawApiCall.getSheetId(modelTableBO.getMdlTblChnsName(),sheetMap) + "!B1:D" + RawApiCall.getSheetMaxRowCnt(modelTableBO.getMdlTblChnsName(),sheetMap));
            JSONArray fields = tblBodyJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");

            int index = 0;
            for (int i = 0; i < fields.size(); i++) {
                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");
                if ("字段注释".equals(rowCol[0])) {
                    index = i+1;
                    break;
                }
            }


            for (int i = index; i < fields.size(); i++) {

                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

                if (i < fields.size()-1){
                    if (rowCol[0] != null && rowCol[1] != null && rowCol[2] != null) {
                        sb.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;").append(rowCol[1]).append(" ").append(rowCol[2]).append(",").append("--").append(rowCol[0]);
                    }else{
                        sb.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;").append("请检查B:D列是否不为空!").append(" ").append(",").append("--").append("请检查B:D列是否不为空!");
                    }
                }else{
                    if (rowCol[0] != null && rowCol[1] != null && rowCol[2] != null) {
                        sb.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;").append(rowCol[1]).append(" ").append(rowCol[2]).append("--").append(rowCol[0]);
                    }else {
                        sb.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;").append("请检查B:D列是否不为空!").append(" ").append("--").append("请检查B:D列是否不为空!");
                    }
                }
            }

            sb.append("<BR />").append(")");
            sb.append("<BR />").append("WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=6, blocksize=65536);");

            //拼接表注释信息
            sb.append("<BR />").append("<BR />").append("comment on table ").append(schema).append(tblEngName).append(" is ").append("'").append(tblComment).append("'").append(";");
            //遍历追加字段注释信息
            for (int i = index; i < fields.size(); i++) {
                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

                sb.append("<BR />").append("comment on column ").append(schema).append(tblEngName).append(".").append(rowCol[1]).append(" is '").append(rowCol[0]).append("';");
            }

            //拼接GP etl 时间参数
            sb.append("<BR />");
            sb.append("<BR />").append("oracle2gp_").append(tblEngName);
            sb.append("<BR />").append("delete from ").append(schema).append(tblEngName).append(" where date = '${azkaban.flow.current.date}'");
            sb.append("<BR />").append("vacuum ").append(schema).append(tblEngName);
            sb.append("<BR />").append("to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as etl_time");
            sb.append("<BR />").append("'${azkaban.flow.current.date}' as \"date\"");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return sb.toString();
    }

    /**
     * @param modelTableBO:
     * @return String
     * @author aochong
     * @description 构建Hive引擎表DDL语句
     * @date 2023/6/26 17:08
     */
    public String budilerHiveTableDdl(ModelTableBO modelTableBO){
         return "目前还没有进行开发扩展，请耐心等待开发者完善！";
    }

    /**
     * @param modelTableBO:
     * @return String
     * @author aochong
     * @description 构建GP引擎表DML语句
     * @date 2023/6/26 20:20
     */
    public String builderGpTableDml(ModelTableBO modelTableBO){

        StringBuilder sb = new StringBuilder("drop table if exists ");
        StringBuilder schema = new StringBuilder();
        StringBuilder sql = new StringBuilder();

        try {
            //获取飞书token
            String tenantToken = RawApiCall.getTenantToken();

            String spreadsheets = modelTableBO.getUrl().split("/")[4].split("\\?")[0];

            //获取工作表
            JSONObject workSheet = RawApiCall.getWorksheet(spreadsheets);

            JSONArray sheets = workSheet.getJSONArray("sheets");
            HashMap<String, JSONObject> sheetMap = new HashMap<>();
            for (int i = 0; i < sheets.size(); i++) {
                JSONObject sheet = sheets.getJSONObject(i);
                sheetMap.put(sheet.getString("title"),sheet);
            }

            String docTitle = RawApiCall.getDocumentInfo(spreadsheets).getJSONObject("data").getJSONObject("spreadsheet").getString("title");

            if (docTitle.contains("EDW")){
                schema.append("dw.");
            }else if(docTitle.contains("ODS")){
                schema.append("ods.");
            }else if(docTitle.contains("DWS")){
                schema.append("dws.");
            }else if(docTitle.contains("ADS")){
                schema.append("ads.");
            }

            //追加schema
            sb.append(schema);

            //获取表名，表注释信息
            JSONObject tblHeaderJson = RawApiCall.getCellValue(spreadsheets, RawApiCall.getSheetId(modelTableBO.getMdlTblChnsName(),sheetMap) + "!B3:C4");
            System.out.println(tblHeaderJson);
            JSONArray headers = tblHeaderJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");

            String tblEngName = "";
            String tblComment = "";

            //表英文名、表中文注释赋值
            for (int i = 0; i < headers.size(); i++) {
                if (i == 0) {
                    tblEngName = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
                    sb.append(tblEngName).append(";");
                    sb.append("<BR />").append("create table if not exists ").append(schema).append(tblEngName).append("(");
                }else if(i == headers.size()-1){
                    tblComment = headers.getString(i).replaceAll("\\[|\\]|\"","").split(",")[1];
                }
            }

            //获取字段信息
            JSONObject tblBodyJson = RawApiCall.getCellValue(spreadsheets, RawApiCall.getSheetId(modelTableBO.getMdlTblChnsName(),sheetMap) + "!B1:O" + RawApiCall.getSheetMaxRowCnt(modelTableBO.getMdlTblChnsName(),sheetMap));
            JSONArray fields = tblBodyJson.getJSONObject("data").getJSONObject("valueRange").getJSONArray("values");

            //StringBuilder对象
            ArrayList<StringBuilder> sqls = new ArrayList<>();
            //过滤条件
            ArrayList<String> conditions = new ArrayList<>();
            //关联条件
            ArrayList<String> associationConditions = new ArrayList<>();
            //(t1,List()),(t2,List())
            TreeMap<String, List<List<String>>> map = new TreeMap<>();

            int index = 0;
            for (int i = 0; i < fields.size(); i++) {
                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",");

                if ("字段注释".equals(rowCol[0])) {
                    index = i+1;
                    break;
                }

                //根据源表列表创建多个不同的SB对象
                if (StringUtils.isNotBlank(rowCol[8]) && rowCol[8].startsWith("t") && StringUtils.isNotBlank(rowCol[9])){
                    sqls.add(new StringBuilder());
                    //将筛选条件加入List
                    conditions.add(rowCol[11]);
                    //根据 表别名(t1) 进行分组创建对象
                    map.put(rowCol[8],new ArrayList<List<String>>());
                }

                //拼接关联条件
                if (StringUtils.isNotBlank(rowCol[6]) && rowCol[6].contains("join") && StringUtils.isNotBlank(rowCol[8])){
                    associationConditions.add(rowCol[6] + " " + rowCol[8]);
                }

            }

            //根据表别名(t1)进行分组赋值
            for (int i = index; i < fields.size(); i++) {
                //这里需要注意表名不能带链接否则逗号切割会有问题
                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",(?=(?:[^']*'[^']*')*[^']*$)");

                if(map.containsKey(rowCol[4])){
                    map.get(rowCol[4]).add(Arrays.asList(rowCol));
                }
            }

            StringBuilder tailSql = new StringBuilder("<BR />select");
            StringBuilder joinSql = new StringBuilder();
            List<String> list2 = new ArrayList<>();
            HashMap<String, Integer> map2 = new HashMap<>();


            //map转迭代器
            Iterator<Map.Entry<String, List<List<String>>>> iterator = map.entrySet().iterator();
            //map遍历下标
            int num = 0;

            HashMap<String, String> fieldMap = new HashMap<>();

            //遍历t1,t2,t3,t4,t5
            while (iterator.hasNext()){
                Map.Entry<String, List<List<String>>> entry = iterator.next();
                List<List<String>> values = entry.getValue();
                if (sqls.size() == map.size()){
                    StringBuilder stringBuilder = new StringBuilder();
                    //利用HashMap Key不允许重复特性判断字段名是否重复

                    //第一行和非第一行判断
                    if (num == 0){
                        sqls.get(num).append("with temp" + (num+1)).append(" as (").append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;select");
                    }else{
                        sqls.get(num).append(", temp" + (num+1)).append(" as (").append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;select");
                    }

                    //遍历每个表下面所有字段行
                    for (int i = 0; i < values.size(); i++) {
                        List<String> lines = values.get(i);
                        //当map集合中不包含重复字段则进入
                        if (!fieldMap.containsKey(lines.get(7))){
                            //下标小于List长度时
                            if (i < values.size()){
                                //判断是否是维度表
                                if(lines.get(5).contains("dim")){
                                    //拼接关联key
                                    if (!fieldMap.containsKey(associationConditions.get(num).substring(associationConditions.get(num).lastIndexOf(".")+1))){
                                        stringBuilder.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + associationConditions.get(num).substring(associationConditions.get(num).lastIndexOf(".")+1)).append(",");
                                    }
                                    stringBuilder.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;").append(lines.get(7)).append(",").append("  --").append(lines.get(6));
                                    fieldMap.put(associationConditions.get(num).substring(associationConditions.get(num).lastIndexOf(".")+1),null);
                                }else{
                                    stringBuilder.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;").append(lines.get(7)).append(" as ").append(lines.get(1)).append(",").append("  --").append(lines.get(6));
                                }
                            }

                            if (num < sqls.size()-1){
                                fieldMap.put(lines.get(7),associationConditions.get(num));
                            }
                        }

                        System.out.println("lines -->" + lines);

//                        tailSql.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;");
//
//                        //判断是否是来源于同一张表同一个字段，主要针对从维度表冗余进来的字段
//                        if (StringUtils.isNotBlank(lines.get(4))){
//                            if (!map2.containsKey(lines.get(4) + "." + lines.get(7))){
//                                if(lines.get(5).contains("dim")){
//                                    tailSql.append(lines.get(4)).append(".").append(lines.get(7)).append(" as ").append(lines.get(1)).append(",").append("  --").append(lines.get(0));
//                                }else{
//                                    tailSql.append(lines.get(4)).append(".").append(lines.get(1)).append(",").append("  --").append(lines.get(0));
//                                }
//                                map2.put(lines.get(4) + "." + lines.get(7),null);
//                            }else{
//                                String alias = lines.get(4);
//                                String prefix = alias.substring(0,1);
//                                String suffix = alias.substring(1);
//
//                                Set<String> keySet = map.keySet();
//                                for (String s : keySet) {
//                                    if (s.equals(prefix + (Integer.parseInt(suffix) + cValue))){
//                                        alias = prefix + (Integer.parseInt(suffix) + (cValue + 1));
//                                    }else{
//                                        alias = prefix + (Integer.parseInt(suffix) + cValue);
//                                    }
//                                }
//
//                                tailSql.append(alias).append(".").append(lines.get(7)).append(" as ").append(lines.get(1)).append(",").append("  --").append(lines.get(0));
//
//                                map2.put(prefix + (Integer.parseInt(suffix) + cValue),null);
//                                cValue += 1;
//                            }
//                        }else{
//                            //源表别名列为空或者为空字符串
//                            //TODO
//                        }

                        if (i == values.size()-1){
                            //删除最后一个逗号
                            if (stringBuilder.length() > 0 && stringBuilder.charAt(stringBuilder.lastIndexOf("-")-4) == ',') {
                                stringBuilder.deleteCharAt(stringBuilder.lastIndexOf("-")-4);
                            }

                            if (!fieldMap.containsKey(lines.get(7))){
                                stringBuilder.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;").append(lines.get(7)).append(" as ").append(lines.get(1)).append("  --").append(lines.get(6));
                            }

                            sqls.get(num).append(stringBuilder);
                            sqls.get(num).append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;from ").append(lines.get(5));
                            if (StringUtils.isNotBlank(conditions.get(num))) {
                                sqls.get(num).append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;where ").append(conditions.get(num));
                            }
                            sqls.get(num).append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;group by ").append(stringBuilder).append("<BR />").append(")");
                        }

                    }
                }
                num++;
            }

            Iterator<Map.Entry<String, String>> iterator1 = fieldMap.entrySet().iterator();
            while (iterator1.hasNext()){
                Map.Entry<String, String> next = iterator1.next();
                System.out.println("key: " + next.getKey());
                System.out.println("value: " + next.getValue());
            }


            HashMap<String, List<String>> map4 = new HashMap<>();
            ArrayList<String> list = new ArrayList<>();

            int cValue = 1;
            //按照顺序重新遍历拼接字符串
            for (int i = index; i < fields.size(); i++) {
                String[] rowCol = fields.getString(i).replaceAll("\\[|\\]|\"", "").split(",(?=(?:[^']*'[^']*')*[^']*$)");
                tailSql.append("<BR />").append("&nbsp;&nbsp;&nbsp;&nbsp;");

//                if (i > 0){
//                    for (int i1 = 0; i1 < associationConditions.size(); i1++) {
//
//                        String joinStr = associationConditions.get(i).substring(0, associationConditions.get(i).indexOf("on") - 4);
//                        list.add(joinStr)
//                    }
//                    String s = fieldMap.get(rowCol[7]);
//
//                    list.add()
//                    map4.put(rowCol[1],list);
//                }else {
//
//                }

                //拼接join语句
//                joinSql.append("<BR />")
//                        .append(joinStr.substring(0,joinStr.indexOf("on")-4))
//                        .append("temp ")
//                        .append(joinStr.substring(joinStr.indexOf("on")-3,joinStr.indexOf("on")-2) + joinCnt)
//                        .append(" on ")
//                        .append(joinStr.substring(joinStr.indexOf("on")+3,joinStr.indexOf("=")))
//                        .append(" = ")
//                        .append("t" + joinCnt)
//                        .append(joinStr.substring(joinStr.lastIndexOf(".")));

                //判断是否是来源于同一张表同一个字段，主要针对从维度表冗余进来的字段
                if (StringUtils.isNotBlank(fields.getString(4))){
                    if (!map2.containsKey(rowCol[4] + "." + rowCol[7])){
                        if(rowCol[5].contains("dim")){
                            tailSql.append(rowCol[4]).append(".").append(rowCol[7]).append(" as ").append(rowCol[1]).append(",").append("  --").append(rowCol[0]);
                        }else{
                            tailSql.append(rowCol[4]).append(".").append(rowCol[1]).append(",").append("  --").append(rowCol[0]);
                        }
                        map2.put(rowCol[4] + "." + rowCol[7],null);
                    }else{
                        String alias = "";
                        String prefix = "";
                        String suffix = "";
                        if(!"null".equals(rowCol[4])){
                            alias = rowCol[4];
                            prefix = alias.substring(0,1);
                            suffix = alias.substring(1);

                            Set<String> keySet = map.keySet();
                            for (String s : keySet) {
                                if (s.equals(prefix + (Integer.parseInt(suffix) + cValue))){
                                    alias = prefix + (Integer.parseInt(suffix) + (cValue + 1));
                                }else{
                                    alias = prefix + (Integer.parseInt(suffix) + cValue);
                                }
                            }

                            tailSql.append(alias).append(".").append(rowCol[7]).append(" as ").append(rowCol[1]).append(",").append("  --").append(rowCol[0]);
                            map2.put(prefix + (Integer.parseInt(suffix) + cValue),null);
                        }else{
                            if (i <= fields.size()-3){
                                tailSql.append("").append(" as ").append(rowCol[1]).append(",").append("  --").append(rowCol[0]);
                            }else{
                                if (!"null".equals(rowCol[10])){
                                    System.out.println("rowCol : " + rowCol[10]);
                                    tailSql.append(rowCol[10]).append(" as ").append(rowCol[0]).append(",").append("  --").append(rowCol[0]);
                                }else {
                                    tailSql.append("").append(" as ").append(rowCol[0]).append(",").append("  --").append(rowCol[0]);
                                }
                            }
                        }

                        cValue += 1;
                    }
                }else{
                    //源表别名列为空或者为空字符串
                    //TODO
                }

                if (i == fields.size()-1){
                    //删除最后一个逗号
                    if (tailSql.length() > 0 && tailSql.charAt(tailSql.lastIndexOf("-")-4) == ',') {
                        tailSql.deleteCharAt(tailSql.lastIndexOf("-")-4);
                    }
                }
            }

            sql.append("begin;")
                    .append("<BR />")
                    .append("delete from ").append(schema).append(tblEngName).append(" where date = '").append("${azkaban.flow.current.date}';").append("<BR />")
                    .append("insert into ").append(schema).append(tblEngName).append("<BR />");

            for (int i = 0; i < sqls.size(); i++) {
                System.out.println("--->" + sqls.get(i));

                if (i < sqls.size()-1){
                    sql.append(sqls.get(i)).append("<BR />");
                }else {
                    sql.append(sqls.get(i));
                }
            }

            sql.append(tailSql);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return sql.toString();
    }

}
